#!/user/bin/env python
#coding:utf-8

import os,sys
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
from PyQt5 import QtWidgets
from regist import *
from data import DB
from query import *
from daoru import *
from filein import *

class Zhuye(QWidget):

    def __init__(self):
        super(Zhuye, self).__init__()
        self.db = DB()
        self.cur = self.db.cur
        self.con = self.db.con
        self.win = 0
        self.setWindowTitle("2019-nCov")
        self.resize(1080, 720)
        self.center()
        self.set_ui()

    def set_ui(self):

        self.welcome_window = Welcome()
        self.stacklayout= QStackedWidget()
        self.stacklayout.addWidget(self.welcome_window)
        self.stacklayout.setCurrentWidget(self.welcome_window)

        self.scroll = QScrollArea(self)
        self.scroll.setAutoFillBackground(True)
        self.scroll.setMinimumSize(900,500)
        self.scroll.setWidgetResizable(True)
        self.scroll_bar = self.scroll.verticalScrollBar()

        self.scroll.setWidget(self.stacklayout)

        self.bar = QMenuBar()
        self.bar.setStyleSheet("font-size:14px;background-color:white;color:black;")

        dengji = self.bar.addMenu("登记")
        add_regist_card = QAction("添加",self)
        add_regist_card.triggered.connect(lambda:self.add_regist(1))
        query_regist_card = QAction("报告卡查询", self)
        query_regist_card.triggered.connect(lambda:self.change_widget(Query()))
        dengji.addAction(add_regist_card)
        dengji.addAction(query_regist_card)

        daoru = self.bar.addMenu("导入")
        daoru_vacci_card = QAction("导入疫苗接种",self)
        daoru_vacci_card.triggered.connect(self.vacci)
        daoru_test_card = QAction("导入核酸检测",self)
        daoru_test_card.triggered.connect(self.test)
        daoru.addAction(daoru_vacci_card)
        daoru.addAction(daoru_test_card)

        xiecha_ = self.bar.addMenu("协查")
        xiecha_card = QAction('协查',self)
        xiecha_card.triggered.connect(self.xiecha)
        xiecha_.addAction(xiecha_card)

        self.toolbar = QToolBar()
        self.toolbar.setStyleSheet('border:none;background-color:white;')
        self.toolbar.setIconSize(QSize(36,36))
        dengji_add_tool = QAction(QIcon("./img/a14.png"),"添加",self)
        dengji_add_tool.triggered.connect(lambda:self.add_regist(1))
        query_tool = QAction(QIcon('./img/a2.png'),'查询',self)
        query_tool.triggered.connect(lambda:self.change_widget(Query()))
        daoru_tool = QAction(QIcon('./img/a5.png'),'导入',self)
        daoru_tool.triggered.connect(self.daoru)
        self.toolbar.addAction(dengji_add_tool)
        self.toolbar.addAction(query_tool)
        self.toolbar.addAction(daoru_tool)
        self.toolbar.addSeparator()
        close_action = QAction(QIcon("./img/a6.png"),"退出系统",self)
        close_action.triggered.connect(lambda:os._exit(5))
        self.toolbar.addAction(close_action)
        self.toolbar.addSeparator()

        author_info = QLabel(">>>黄山区疾控中心@1371205963@qq.com<<<")
        author_info.setAlignment(Qt.AlignCenter)
        status_widget = QGroupBox()
        status_widget.setStyleSheet('background-color:white;color:black;font-size:12px;border:none;')
        status_layout = QHBoxLayout()
        status_layout.addWidget(author_info)
        status_widget.setLayout(status_layout)

        self.mainlayout = QVBoxLayout()
        self.mainlayout.addWidget(self.bar)
        self.mainlayout.addWidget(self.toolbar)
        self.mainlayout.addWidget(self.scroll)
        self.mainlayout.addWidget(status_widget)
        self.setLayout(self.mainlayout)

    def change_widget(self, widget):
        new_widget = widget
        self.stacklayout.addWidget(new_widget)
        self.stacklayout.setCurrentWidget(new_widget)

    def add_regist(self,id):
        if self.win:
            self.win.close()
        self.win = Regist()
        self.win.show()
        self.win.name.setFocus()

    def vacci(self):
        filename,ok = QFileDialog.getOpenFileName(self, '选择数据','./')
        if filename:
            try:
                workbook = xlrd.open_workbook(filename)
                sheet = workbook.sheets()[0]
                rows = sheet.nrows
                for i in range(1,rows):
                    tmp = sheet.row_values(i)
                    for i in [3,5,7,9,11,13]:
                        tmp[i] = self.change_date(tmp[i])
                    try:
                        vaccine_sql = '''insert into vaccine values ("{}","{}",'{}',{},'{}',{},
                                '{}',{},'{}',{},"{}",
                                {},"{}",{},'{}')
                                '''.format(*tmp)
                        self.cur.execute(vaccine_sql)
                    except:
                        tmp2 = tmp[2:] + [tmp[0],]
                        vaccine_sql = '''update vaccine set is_vaccined = '{}',dose1_date = {},
                                dose1_name = '{}',dose2_date = {},dose2_name = '{}',
                                dose3_date = {},dose3_name = '{}',booster_shot1_date = {},
                                booster_shot1_name = "{}",booster_shot2_date = {},
                                booster_shot2_name = "{}",booster_shot3_date = {},
                                booster_shot3_name = '{}' where id = "{}"
                                '''.format(*tmp2)
                        self.cur.execute(vaccine_sql)
                    self.con.commit()
                    self.messagebox_auto_close()
            except:
                self.messagebox_auto_close("表格有误！")


    def test(self):
        filename,ok = QFileDialog.getOpenFileName(self, '选择数据','./')
        if filename:
            try:
                workbook = xlrd.open_workbook(filename)
                sheet = workbook.sheets()[0]
                rows = sheet.nrows
                for i in range(1,rows):
                    tmp = sheet.row_values(i)
                    tmp[5] = 'ORF1ab:{},N:{}'.format(tmp[5],tmp[6])
                    tmp[6] = tmp[7]
                    tmp[7] = tmp[8]
                    tmp[2] = self.change_date(tmp[2])
                    tmp[4] = self.change_date(tmp[4])
                    dup_sql = 'select * from test where uuid = "{}" and result_date= "{}"'.format(tmp[1],tmp[4])
                    self.cur.execute(dup_sql)
                    dup = self.cur.fetchall()
                    if not dup:
                        test_sql = '''insert into test values ("{}","{}",{},"{}",{},
                                "{}",'{}','{}')
                                '''.format(*tmp)
                        self.cur.execute(test_sql)
                        print(test_sql)
                self.con.commit()
                self.messagebox_auto_close()
            except:
                self.messagebox_auto_close("表格有误！")

    def xiecha(self):
        if self.win:
            self.win.close()
        self.win = Xiecha()
        self.win.show()

    def change_date(self,time):
        try:
            tmp = (time-70*365-19)*86400
        except:
            tmp = 1000000
        return tmp

    def daoru(self):
        if self.win:
            self.win.close()
        # self.win = Daoru()
        self.win = FileIn()
        self.win.show()

    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()

        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )

    def keyPressEvent(self, e):
        if e.key() == Qt.Key_Escape:
            self.close()

    def messagebox_auto_close(self,txt = '完成!!!',time =1):
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("# WARNING: ")
        msg.setText(txt)
        msg.setStandardButtons(QMessageBox.Yes)
        msg.button(QMessageBox.Yes).animateClick(time*1000)
        msg.exec_()

class Welcome(QWidget):

    def __init__(self):
        super(Welcome, self).__init__()
        self.resize(800, 400)
        mainlayout = QHBoxLayout()
        a = QGroupBox()
        a.setStyleSheet("background-image:url(./img/bg.jpg);background-repeat:no-repeat;background-position:center;border:none;")
        layout = QVBoxLayout()
        layout.addWidget(a)
        self.setLayout(layout)


class Auto_Msg(QWidget):

    def __init__(self,msg='DONE!!!',time=1):
        super(Auto_Msg,self).__init__()
        self.msg = msg
        self.time = time
        self.messagebox_auto_close(self.msg,self.time)

    def messagebox_auto_close(self,txt,time):
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("# WARNING: ")
        msg.setText(txt)
        msg.setStandardButtons(QMessageBox.Yes)
        msg.button(QMessageBox.Yes).animateClick(time*1000)
        msg.exec_()


class Xiecha(QWidget):

    def __init__(self):
        super(Xiecha,self).__init__()
        self.setWindowTitle("协查")
        self.center()
        self.resize(300,80)
        self.db = DB()
        self.cur = self.db.cur
        self.con = self.db.con
        self.set_ui()

    def set_ui(self):
        # self.begin_date_label = QLabel("起始日期")
        # self.begin_date = QDateEdit()
        # self.begin_date.setDate(QDate.currentDate().addMonths(-1))
        # self.begin_date_bnt = QPushButton()
        # self.begin_date_bnt.setStyleSheet("border:hidden;")
        # self.begin_date_bnt.setIcon(QIcon('./img/cal.ico'))
        # self.begin_date_bnt.clicked.connect(lambda:self.show_cal(self.begin_date))
        # self.end_date_label = QLabel("截止日期")
        # self.end_date = QDateEdit()
        # self.end_date.setDate(QDate.currentDate().addDays(1))
        # self.end_date_bnt = QPushButton()
        # self.end_date_bnt.setStyleSheet("border:hidden;")
        # self.end_date_bnt.setIcon(QIcon('./img/cal.ico'))
        # self.end_date_bnt.clicked.connect(lambda:self.show_cal(self.end_date))
        self.start = QLineEdit()
        self.start.setPlaceholderText('请输入协查起始流水号')

        self.bnt = QPushButton("导出")
        self.bnt.clicked.connect(self.export)
        self.close_bnt = QPushButton("关闭")
        self.close_bnt.clicked.connect(self.close_win)
        self.start.setStyleSheet("width:120px;height:40px;color:black;background-color:None;border:hidden;")
        self.bnt.setStyleSheet("width:60px;height:40px;color:white;background-color:green;border:hidden;")
        self.close_bnt.setStyleSheet("width:60px;height:40px;color:white;background-color:red;border:hidden;")

        layout = QHBoxLayout()
        layout.addWidget(self.start)
        layout.addWidget(self.bnt)
        layout.addWidget(self.close_bnt)
        self.setLayout(layout)

    def export(self):
        try:
            bianhao = int(self.start.text())
        except:
            bianhao = 0
        sql = '''select person.*,relation.*,xiecha.* from person,relation,xiecha where
        person.id = relation.uuid and person.id  = xiecha.id and
        xiecha.bianhao >= {}'''.format(bianhao)
        sql = ''' SELECT person1.name AS patient_name,person1.id AS id2,
            	person2.name,relation.relationship,person2.id,
                person2.gender,person2.age,person2.occup,person2.tel,person2.address,
                relation.contact_place,relation.contact_date,relation.contact_level,
                relation.contact_freq,person2.present_measure,xiecha.type
            FROM
            	relation,
            	person AS person1,
            	person AS person2,
                xiecha
            WHERE
            	relation.contact_uuid = person1.uuid
            	AND relation.uuid = person2.uuid
            	AND xiecha.id = relation.uuid
                and xiecha.bianhao >= {}
            '''.format(bianhao)
        print(sql)
        self.cur.execute(sql)
        rslt = self.cur.fetchall()
        print(rslt)
        if rslt:
            workbook = xlwt.Workbook(encoding='utf-8')
            sheet = workbook.add_sheet("Sheet1")
            head =  ['风险人员来源省','来源市','来源区县','人员类别','感染类型','感染类型',
                '身份证','采样日期','密接判定日期','发病日期','复阳日期','创建省','创建市',
                '创建县','提出机构','机构电话','风险人员','手机','身份证','身份证',
                '省','市','县','详细小区','交通信息','最后暴露日期','暴露方式','备注']
            cols = len(head)
            rows = len(rslt)
            for col in range(cols):
                sheet.write(0,col,head[col])
            k = 1
            for tmp in rslt:
                tmp2 = ['','','',tmp[15],'人员',tmp[0],'公民身份号码',tmp[1],'',
                '','','','','','','','',tmp[2],tmp[8],'公民身份号码',tmp[4],'',
                '','',tmp[9],'',self.to_str_date(tmp[11]),tmp[12]]
                for i in range(cols):
                    sheet.write(k,i,tmp2[i])
                k += 1
            filename,ok = QFileDialog.getSaveFileName(self,'保存结果文件','协查一览表.xls','Excel Files (*.xls)')
            if filename:
                workbook.save(filename)
                self.messagebox_auto_close("保存成功")

    def show_cal(self,tmp):
        self.cal = Calendar(tmp)
        self.cal.show()
        self.cal.date_signal.connect(self.input_date)

    def input_date(self, list):
        list[0].setDate(list[1])

    def to_str_date(self,t):
        if t:
            riqi = datetime.datetime.fromtimestamp(t)
            riqistr = '{}{}{}'.format(str(riqi.year),str(riqi.month).zfill(2),str(riqi.day).zfill(2))
        else:
            riqistr = ''
        return riqistr

    def close_win(self):
        self.db.con.close()
        self.close()

    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()

        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )

    def keyPressEvent(self, e):
        if e.key() == Qt.Key_Escape:
            self.close()

    def messagebox_auto_close(self,txt = '完成!!!',time =1):
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("# WARNING: ")
        msg.setText(txt)
        msg.setStandardButtons(QMessageBox.Yes)
        msg.button(QMessageBox.Yes).animateClick(time*1000)
        msg.exec_()


if __name__ == "__main__":
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon('./img/barcode.ico'))
    win = Zhuye()
    win.show()
    sys.exit(app.exec_())
